Skip to main content
Version: Next

Secondary indexes

This guide provides step-by-step instructions for creating and using secondary indexes in DefraDB to improve query performance.

Key Points

DefraDB's secondary indexing system enables efficient document lookups using the @index directive on GraphQL schema fields. Indexes trade write overhead for significantly faster read performance on filtered queries.

Best practices: Index frequently filtered fields, avoid indexing rarely queried fields, and use unique indexes sparingly (they add a read operation on every write). Plan indexes based on query patterns to balance read/write performance.

Prerequisites​

Before following this guide, ensure you have:

Create a basic index​

Add the @index directive to a field in your schema to create an index.

Index a single field​

type User {
  name: String @index
  age: Int
}

This creates an ascending (ASC) index on the name field.

Specify index direction​

type User {
  name: String @index(direction: DESC)
  age: Int
}

Use direction: DESC for descending order or direction: ASC (default) for ascending order.

note

Direction plays a significant role only for composite indexes. For single-field indexes, the fetcher can traverse entries in either direction equally efficiently.

Add the schema​

defradb client schema add -f schema.graphql

Manage indexes with the CLI​

Indexes can be added or deleted at any time using CLI commands — you do not need to redefine the schema from scratch.

# Create an index on an existing collection
defradb client index create --collection User --fields name

# Create a unique index
defradb client index create --collection User --fields email --unique

# Drop an index
defradb client index drop --collection User --name <index_name>

# List indexes on a collection
defradb client index list --collection User
note

GraphQL-based index management is not yet available. Use the CLI or embedded client.

Create a unique index​

Unique indexes ensure no two documents have the same value for the indexed field.

type User {
  email: String @index(unique: true)
  name: String
}

This prevents duplicate email addresses in your User collection.

Create a composite index​

Composite indexes span multiple fields, useful for queries filtering on multiple fields simultaneously.

Using schema-level directive​

type User @index(includes: [{field: "name"}, {field: "age"}]) {
  name: String
  age: Int
  email: String
}

Specify different directions per field​

type User @index(includes: [
  {field: "name", direction: ASC},
  {field: "age", direction: DESC}
]) {
  name: String
  age: Int
}
note

A composite index is only used when the query filters on the leading field(s) of the index. Filtering on only a non-leading field (e.g. age alone in the example above) will not use this index at all.

Index relationships​

Index relationship fields to improve query performance across related documents.

Basic relationship index​

type User {
  name: String
  age: Int
  address: Address @primary @index
}

type Address {
  user: User
  city: String @index
  street: String
}

This indexes both:

  • The relationship between User and Address
  • The city field in Address

Query with relationship index​

query {
  User(filter: {
    address: {city: {_eq: "Montreal"}}
  }) {
    name
  }
}

With the indexes, DefraDB:

  1. Quickly finds Address documents with city = "Montreal"
  2. Retrieves the related User documents efficiently

Enforce unique relationships​

Use a unique index to enforce one-to-one relationships:

type User {
  name: String
  age: Int
  address: Address @primary @index(unique: true)
}

type Address {
  user: User
  city: String
  street: String
}

This ensures no two Users can reference the same Address document. Note that 1-to-2-sided relations are automatically constrained by a unique index to enforce the 1-to-1 invariant.

Index JSON fields​

DefraDB supports indexing JSON fields for efficient queries on nested data.

Storage warning: Indexing JSON fields can consume significant disk space with large datasets, as every leaf node at every path is indexed separately.

Define a schema with JSON field​

type Product {
  name: String
  metadata: JSON @index
}

Query nested JSON paths​

query {
  Product(filter: {
    metadata: {
      user: {
        device: {
          model: {_eq: "iPhone"}
        }
      }
    }
  }) {
    name
  }
}

The index enables direct lookup of documents matching the nested path and value.

Name your indexes​

Assign custom names to indexes for easier identification.

type User {
  name: String @index(name: "user_name_idx")
  email: String @index(name: "user_email_unique_idx", unique: true)
}

Default names are auto-generated from field names and direction.

Query patterns for best performance​

Index frequently filtered fields​

type Article {
  title: String
  content: String
  status: String @index  # Frequently filtered
  publishedAt: DateTime @index  # Frequently filtered
  author: String
}

Index fields commonly used in filter clauses.

Use composite indexes for multi-field filters​

type Article @index(includes: [
  {field: "status"},
  {field: "publishedAt"}
]) {
  title: String
  status: String
  publishedAt: DateTime
}
query {
  Article(filter: {
    status: {_eq: "published"}
    publishedAt: {_gt: "2017-07-23T03:46:56-05:00"}
  }) {
    title
  }
}

This composite index efficiently handles queries filtering on both status and publishedAt together. If you only filter on publishedAt alone, this index won't be used — add a separate single-field index on publishedAt if that query pattern is also common.

Avoid over-indexing​

Every index adds write overhead, so only index fields that are actually queried. Fields like middleName or internalNote that are rarely used in filters don't need indexes.

Performance considerations​

Analyze your application's queries and index the fields used in filters. Use the explain systems to verify that indexes are being used as expected.

Unique indexes should be used only when uniqueness is a hard requirement — they require an additional read on every insert and update. For JSON and array fields, be mindful that indexing large datasets can consume significant disk space.

Troubleshooting​

Queries still slow after adding indexes​

Issue: Query performance hasn't improved after adding indexes.

Solutions:

  • Verify the index was created successfully using defradb client index list
  • Ensure your query filter uses the indexed field
  • For composite indexes, confirm you are filtering on the leading field
  • Check if you're querying in the reverse direction of a relationship (may need to index the other side)

Unique constraint violations​

Issue: Cannot insert documents due to unique index constraint.

Solution: Check for existing documents with the same value. Unique indexes prevent duplicates, so you must either update the existing document or use a different value.

Write performance degraded​

Issue: Document creation/updates are slower after adding indexes.

Solution: This is expected — indexes trade write performance for read performance. Review your indexes and remove any that aren't serving active query patterns.

Secondary indexes
Edit this page